package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.stereotype.Repository;
import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.global.util.StringUtils;
import com.bringway.p2p.dao.VipDao;
import net.sf.json.JSONObject;

/*
 * 
 * @author fan
 * @className VipDaoImpl
 * @description vip数据库操作接口实现类
 * @date 2015年12月9日上午11:08:58
 */
@Repository
public class VipDaoImpl extends P2PJdbcDao implements VipDao {

	@Override
	public List vipczQuery(JSONObject parms) {
		int start = parms.optInt("start");
		int limit = parms.optInt("limit");
		String startDate = parms.optString("startDate");
		String endDate = parms.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String username = parms.optString("username");
		String tjr = parms.optString("tjr");
		String sjhm = parms.optString("sjhm");
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT t1.USERNAME, date_format(t1.CZSJ,'%Y-%m-%d %H:%i:%s') CZSJ,  t2.SJHM, t2.TJR , date_format(t1.GQSJ,'%Y-%m-%d %H:%i:%s') GQSJ, t1.VIP_STATE  FROM t_zhfw_vipcz t1 LEFT JOIN t_zhfw_user t2 ON t1.USERNAME = t2.USER_NAME WHERE 1=1");
		
		if (StringUtils.isNotEmpty(username)) {
			sql.append(" and t1.USERNAME like :username ");
			parmsMap.put("username", "%"+username+"%");
		}
		if (StringUtils.isNotEmpty(tjr)) {
			sql.append(" and t2.TJR like :tjr ");
			parmsMap.put("tjr", "%"+tjr+"%");
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" and t2.SJHM like :sjhm");
			parmsMap.put("sjhm", "%"+sjhm+"%");
		}
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and t1.CZSJ >= :startDate ");
			parmsMap.put("startDate", startDate+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and t1.CZSJ <= :endDate ");
			parmsMap.put("endDate", endDate+" 23:59:59");
		}
		sql.append(" order by t1.CZSJ desc");
		if (start>=0&&limit>0) {
			sql.append("  limit :start,:limit");
			parmsMap.put("start", start);
			parmsMap.put("limit", limit);
		}
		System.err.println(sql.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), parmsMap);
	}

	@Override
	public int vipGetPageCount(JSONObject parms) {
		int start = parms.optInt("start");
		int limit = parms.optInt("limit");
		String startDate = parms.optString("startDate");
		String endDate = parms.optString("endDate");
		String username = parms.optString("username");
		String tjr = parms.optString("tjr");
		String sjhm = parms.optString("sjhm");
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		parmsMap.put("start", start);
		parmsMap.put("limit", limit);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT count(*) FROM  t_zhfw_vipcz t1 LEFT JOIN t_zhfw_user t2 ON t1.USERNAME = t2.USER_NAME  WHERE 1=1 ");
		
		if (StringUtils.isNotEmpty(username)) {
			sql.append(" and t1.USERNAME like :username ");
			parmsMap.put("username", "%"+username+"%");
		}
		if (StringUtils.isNotEmpty(tjr)) {
			sql.append(" and t2.TJR like :tjr ");
			parmsMap.put("tjr", "%"+tjr+"%");
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" and t2.SJHM like :sjhm");
			parmsMap.put("sjhm", "%"+sjhm+"%");
		}
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and t1.CZSJ >= :startDate ");
			parmsMap.put("startDate", startDate+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and t1.CZSJ <= :endDate ");
			parmsMap.put("endDate", endDate+" 23:59:59");
		}
		sql.append(" order by t1.CZSJ desc ");
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), parmsMap,Integer.class);
	}

}
